package com.yueyue.javafx.controller;

import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.scene.control.Alert;
import javafx.scene.control.ComboBox;
import javafx.scene.control.Label;
import javafx.stage.DirectoryChooser;
import javafx.stage.FileChooser;
import javafx.stage.Stage;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Set;


@Slf4j
@Component
public class MainController {

    @FXML
    public Label labelOutput1;

    @FXML
    public Label labelSource;

    @FXML
    private ComboBox<String> letterComboBox;


    private File selectedDirectory;

    private File  fileSource;

    @FXML
    void btnOnClickSubmit(ActionEvent event) {
        String selectedLetter = letterComboBox.getValue();
        try {
            parseExcel(fileSource, selectedLetter, selectedDirectory);
        }catch (IOException e){
            showAlert("解析结果", "失败",Alert.AlertType.ERROR);
        }
        showAlert("解析结果", "成功",Alert.AlertType.INFORMATION);
    }

    public void parseExcel(File source,String col,File dest) throws IOException {
        log.info("传递进来的三个参数为{}，{}，{}",source,col,dest);
        if (source == null || col == null || col.isEmpty() || dest == null) {
            showAlert("参数校验结果", "参数有误，请重新输入",Alert.AlertType.ERROR);
            throw new IllegalArgumentException("One or more parameters are null or empty.");
        }

        // Load the source Excel file
        FileInputStream fis = new FileInputStream(source);
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);

        // Determine the column index from the column letter
        int colIndex = CellReference.convertColStringToIndex(col);

        // Extract unique values from the specified column
        Set<String> uniqueValues = new HashSet<>();
        Iterator<Row> rowIterator = sheet.iterator();
        rowIterator.next(); // Skip header row

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Cell cell = row.getCell(colIndex);
            if (cell != null) {
                uniqueValues.add(getCellValueAsString(cell));
            }
        }

        // Create the destination directory if it does not exist
        if (!dest.exists()) {
            dest.mkdirs();
        }

        // Filter data and export to separate files
        for (String value : uniqueValues) {
            Workbook newWorkbook = new XSSFWorkbook();
            Sheet newSheet = newWorkbook.createSheet("FilteredData");

            // Copy header
            Row headerRow = newSheet.createRow(0);
            Row sourceHeaderRow = sheet.getRow(0);
            for (int i = 0; i < sourceHeaderRow.getLastCellNum(); i++) {
                Cell sourceCell = sourceHeaderRow.getCell(i);
                Cell newCell = headerRow.createCell(i);
                if (sourceCell != null) {
                    newCell.setCellValue(getCellValueAsString(sourceCell));
                }
            }

            // Copy filtered rows
            int newRowNum = 1;
            rowIterator = sheet.iterator();
            rowIterator.next(); // Skip header row again
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Cell cell = row.getCell(colIndex);
                if (cell != null && getCellValueAsString(cell).equals(value)) {
                    Row newRow = newSheet.createRow(newRowNum++);
                    for (int i = 0; i < row.getLastCellNum(); i++) {
                        Cell sourceCell = row.getCell(i);
                        Cell newCell = newRow.createCell(i);
                        if (sourceCell != null) {
                            switch (sourceCell.getCellType()) {
                                case STRING:
                                    newCell.setCellValue(sourceCell.getStringCellValue());
                                    break;
                                case NUMERIC:
                                    newCell.setCellValue(sourceCell.getNumericCellValue());
                                    break;
                                case BOOLEAN:
                                    newCell.setCellValue(sourceCell.getBooleanCellValue());
                                    break;
                                case FORMULA:
                                    newCell.setCellFormula(sourceCell.getCellFormula());
                                    break;
                                default:
                                    break;
                            }
                        }
                    }
                }
            }

            // Write the new workbook to a file
            FileOutputStream fos = new FileOutputStream(new File(dest, "Filtered_" + value + ".xlsx"));
            newWorkbook.write(fos);
            fos.close();
            newWorkbook.close();
        }

        // Close the source workbook
        workbook.close();
        fis.close();
    }

    // Utility method to get cell value as a string
    //将单元格的值转换为字符串
    private static String getCellValueAsString(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return "";
        }
    }


    @FXML
    public void initialize() {
        // 初始化字母列表A-Z
        ObservableList<String> letters = FXCollections.observableArrayList();
        for (char letter = 'A'; letter <= 'Z'; letter++) {
            letters.add(String.valueOf(letter));
        }
        letterComboBox.setItems(letters);
        letterComboBox.setVisibleRowCount(6);

        // 处理用户自定义输入
        letterComboBox.setEditable(true);
        letterComboBox.getEditor().textProperty().addListener((obs, oldValue, newValue) -> {
            if (newValue != null && !newValue.isEmpty() && !letters.contains(newValue)) {
                //letters.add(newValue);
               // letterComboBox.setItems(letters); // 更新ComboBox项以反映新的输入值
            }
        });
    }

    @FXML
    void onClickOutPut(ActionEvent event) {
        DirectoryChooser directoryChooser = new DirectoryChooser();
        directoryChooser.setTitle("Select Folder");

        // Assuming that this method is called from a window that is part of a Stage
        Stage stage = (Stage)((javafx.scene.Node)event.getSource()).getScene().getWindow();
        selectedDirectory = directoryChooser.showDialog(stage);
        System.out.println(selectedDirectory.getAbsolutePath());

        if (selectedDirectory != null) {
            String folderPath = selectedDirectory.getAbsolutePath();
            //showAlert("Selected Folder", "Folder Path: " + folderPath);
            labelOutput1.setText(selectedDirectory.getAbsolutePath());
        } else {
            //showAlert("No Selection", "No folder selected.");
        }
    }

    private void showAlert(String title, String content,Alert.AlertType alertType) {
        Alert alert = new Alert(alertType);
        alert.setTitle(title);
        alert.setHeaderText(null);
        alert.setContentText(content);
        alert.showAndWait();
    }

    @FXML
    void onClickSource(ActionEvent event) {
        FileChooser fileChooser = new FileChooser();
        fileChooser.setTitle("寻找到你想要拆分的Excel源文件 ");
        fileChooser.getExtensionFilters().addAll(
                new FileChooser.ExtensionFilter("Excel Files", "*.xlsx")
        );
        Stage stage = (Stage)((javafx.scene.Node)event.getSource()).getScene().getWindow();
        fileSource=fileChooser.showOpenDialog(stage);
        labelSource.setText(fileSource.toString());
        System.out.println(fileSource);
    }
}